package com.ks.sgms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ks.sgms.entity.Score;
import com.ks.sgms.entity.Student;
import com.ks.sgms.util.DbHelper;

public class StudentDao {
	public List<Student> getAllStudent() { 
		List<Student> list = new ArrayList<Student>(); 
		Connection conn = DbHelper.getConnection();
		String sql = "select student_id,student_name,class_name,sex,birth"
				+ " from student,class"
				+ " where student.class_id = class.class_id"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Student student = new Student();
				student.student_id = rst.getString("student_id");
				student.student_name = rst.getString("student_name");
				student.class_name = rst.getString("class_name");
				student.sex = Integer.parseInt(rst.getString("sex"));
				student.birthString = rst.getString("birth");
				list.add(student);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace(); 
		}
		return list;
	}
	
	public Student selectStuByNo(String stuNo) {
		Connection conn = DbHelper.getConnection();
		String sql = "select student_id,student_name,class_name,sex,birth,student.class_id"
				+ " from student,class"
				+ " where student.class_id = class.class_id"
				+ " and student.student_id = ?"; 
		Student student = new Student();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, stuNo);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				student.student_id = rst.getString("student_id");
				student.student_name = rst.getString("student_name");
				student.class_name = rst.getString("class_name");
				student.class_id = rst.getString("class_id");
				student.sex = Integer.parseInt(rst.getString("sex"));
				student.birthString = rst.getString("birth");
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}
	
	public List<Student> selectStuByClass(String class_name) {
		//System.out.println(class_name);
		Connection conn = DbHelper.getConnection();
		String sql = "select student_id,student_name,class_name,sex,birth"
				+ " from student,class"
				+ " where student.class_id = class.class_id"
				+ " and student.class_id = ("
				+ "select class_id from class where class_name = ?"
				+ ")"; 
		List<Student> list = new ArrayList<Student>();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, class_name);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Student student = new Student();
				student.student_id = rst.getString("student_id");
				student.student_name = rst.getString("student_name");
				student.class_name = rst.getString("class_name");
				student.sex = Integer.parseInt(rst.getString("sex"));
				student.birthString = rst.getString("birth");
				list.add(student);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public Student selectStuByIdAndClass(String student_id,String class_name) {
		//System.out.println(class_name);
		Connection conn = DbHelper.getConnection();
		String sql = "select student_id,student_name,class_name,sex,birth"
				+ " from student,class"
				+ " where student.class_id = class.class_id"
				+ " and student.student_id = ?"
				+ " and student.class_id = ("
				+ "	select class_id from class where class_name = ?"
				+ ")"; 
		Student student = new Student();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, class_name);
			pst.setString(2, student_id);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				student.student_id = rst.getString("student_id");
				student.student_name = rst.getString("student_name");
				student.class_name = rst.getString("class_name");
				student.sex = Integer.parseInt(rst.getString("sex"));
				student.birthString = rst.getString("birth");
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}
	
	public boolean addStu(Student student) {
		String sql = "INSERT INTO student(student_id,student_name,sex,birth,class_id) VALUES (?,?,?,?,?)"; 
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, student.student_id);
			pst.setString(2, student.student_name);
			pst.setString(3, student.sex+"");
			pst.setString(4, student.birthString);
			pst.setString(5, student.class_id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean deleteStu(String stuNo) {
		String sql = "DELETE FROM student WHERE student_id = ?";
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, stuNo);
			int count = pst.executeUpdate();
			pst.close();
			System.out.println("删除"+count+"个学生");
			return count > 0 ? true : false; 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	public boolean updateStudent(Student s) {
		String sql = "UPDATE student SET student_id=?,student_name=?,class_id=?,birth=?,sex=? where student_id = ?";
		Connection connection = DbHelper.getConnection();
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, s.student_id);
			pst.setString(2, s.student_name);
			pst.setString(3, s.class_id);
			pst.setString(4, s.birthString);
			pst.setString(5, s.sex+"");
			pst.setString(6, s.student_id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
